﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Linq.Expressions;
using Extentions;
using Microsoft.AspNetCore.Http;
using Microsoft.Data.SqlClient;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Storage;
using TrainingReminderSystem.Dal;
using TrainingReminderSystem.Model;

namespace TrainingReminderSystem.Bll
{
    /// <summary>
    /// 逻辑层
    /// </summary>
    public partial class SysStatisticalSqlBll : PcLoginedBll
    {
        #region 预览,总条数,导出

        /// <summary>
        /// 预览
        /// 返回类型为DataTable,最外层的swagger会报错
        /// </summary>
        /// <param name="id"></param>
        /// <param name="topVal"></param>
        /// <param name="canOrderBy"></param>
        /// <param name="asc"></param>
        /// <param name="orderBy"></param>
        /// <returns></returns>
        public AjaxResult<dynamic> SearchBySql(int id, int topVal, bool canOrderBy, bool asc, string orderBy)
        {
            var r = new AjaxResult<dynamic>();
            if (topVal <= 0 || topVal > 1000)
            {
                r.Message = "预览条数只能是大于0小于等于1000的数字";
                return r;
            }

            using var db = Db;
            var sql = db.SysStatisticalSql.AsNoTracking().Where(m => m.ID == id).Select(m => m.SqlContent).FirstOrDefault();
            if (string.IsNullOrEmpty(sql))
            {
                r.Message = "未找到相关sql语句";
                return r;
            }

            if (canOrderBy)
            {
                sql = $@"select top {topVal} 
M.* from (
{sql}
) as M order by 
{orderBy} 
{(asc ? "asc" : "desc")}";
            }
            else
            {
                sql = $@"select top {topVal} 
M.* from (
{sql}
) as M";
            }

            try
            {
                var dt = db.Database.ExSqlQuery(sql);
                r.Result = dt;
                var listColumns = dt.Columns.Cast<DataColumn>().Select(m => m.ColumnName).ToList();
                r.Message = listColumns.ExToJson();
            }
            catch (Exception ex)
            {
                r.Message = "sql语句报错:" + ex.ExFullString();
                return r;
            }

            r.Success = true;
            return r;
        }

        /// <summary>
        /// 总条数
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public AjaxResult<int> TotalBySql(int id)
        {
            AjaxResult<int> r = new AjaxResult<int>();
            using var db = Db;
            var sql = db.SysStatisticalSql.AsNoTracking().Where(m => m.ID == id).Select(m => m.SqlContent).FirstOrDefault();
            if (string.IsNullOrEmpty(sql))
            {
                r.Message = "未找到相关sql语句";
                return r;
            }

            sql = $@"select count(1) from (
{sql}
) as M";
            try
            {
                var val = db.Database.ExSqlQuery<int>(sql);
                r.Result = val.FirstOrDefault();
            }
            catch (Exception ex)
            {
                r.Message = "sql语句报错:" + ex.ExFullString();
                return r;
            }
            r.Success = true;
            return r;
        }

        /// <summary>
        /// 导出
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public AjaxResult<string> ExportBySql(int id)
        {
            var r = new AjaxResult<string>();
            using var db = Db;
            var info = db.SysStatisticalSql.AsNoTracking().Where(m => m.ID == id).Select(m => new { m.SqlContent, m.StatisticalSqlName }).FirstOrDefault();
            if (info == null || string.IsNullOrEmpty(info.SqlContent))
            {
                r.Message = "未找到相关sql语句";
                return r;
            }
            string sql = $@"select M.* from (
{info.SqlContent}
) as M";
            var dt = db.Database.ExSqlQuery(sql);
            string filePath = ExportHelper.TableToExcel(dt, info.StatisticalSqlName);
            r.Result = ExHttpContext.ServerHost.ExRemoveLast() + filePath;
            r.Success = true;
            return r;
        }

        #endregion
    }
}
